﻿using AutoMapper;
using MediatR;
using Microsoft.Data.SqlClient;
using S.Transfer.API.Application.Command.TransferCommand;
using S.Transfer.Domain.Model;
using S.Transfer.Infrastructure;
using S.Transfer.Infrastructure.Interface.Transfer;
using System.Security.Policy;

namespace S.Transfer.API.Application.CommandHandlers.TransferCommandHandler
{
    public class TransferByIdCommandHandler : IRequestHandler<TransferByIdCommand, int>, IRequestHandler<TransferByIdEarCommand, int>,IRequestHandler<TransferByTypeEarCommand,int>,IRequestHandler<TransFerUpdateCommand, int>,IRequestHandler<TransFerDeleteMore,string>
    {
        private readonly DbHelper db;
        private readonly ITransferModellRepository translator;
        private readonly IMapper mapper;
        public TransferByIdCommandHandler(ITransferModellRepository _translator, DbHelper _db,IMapper _mapper)
        {
            this.translator = _translator;
            this.db = _db;
            this.mapper = _mapper;
        }
        public async Task<int> Handle(TransferByIdCommand request, CancellationToken cancellationToken)
        {
            string sql = "SELECT TOP 1 * FROM [dbo].[SheepRescission]  WHERE EarTag=@EarTag ORDER BY SurrenderDate DESC ";
            var parameters = new List<SqlParameter>
            {
                     new SqlParameter("@EarTag",request.EarTag),
            };
            int result = await db.ExecuteNonQuery(sql, parameters.ToArray());
            return result;
        }
        /// <summary>
        /// 删除方法
        /// </summary>
        /// <param name="request"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public async Task<int> Handle(TransferByIdEarCommand request, CancellationToken cancellationToken)
        {
            string sql = "SELECT TOP 1 * FROM [dbo].[SheepRescission] WHERE EarTag=@EarTag ORDER BY SurrenderDate DESC ";
            var parameters = new List<SqlParameter>
            {
                    new SqlParameter("@EarTag", request.EarTag),
            };
            var result = await db.GetListDataBySql<SheepRescission>(sql, parameters.ToArray());

            // 判断是否存在数据
            if (result!=null)
            {
                foreach (var item in result)
                {
                    // 进行比较，如果当前耳号与最新的耳号相等，则调用删除方法
                    if (request.SurrenderDate ==item.SurrenderDate)
                    {
                        string sqls = @"DELETE FROM [dbo].[SheepRescission] 
                                        WHERE EarTag =@EarTag 
                                        AND SurrenderDate = (
                                          SELECT MAX(SurrenderDate) 
                                          FROM [dbo].[SheepRescission] 
                                          WHERE EarTag =@EarTag
                                        )";
                        var parameter = new List<SqlParameter>
                        {
                                new SqlParameter("@EarTag", request.EarTag),
                        };
                        var results = await db.ExecuteNonQuery(sqls, parameter.ToArray());
                        return results;
                    }

                }
            }
            return 0;
        }

        public async Task<int> Handle(TransferByTypeEarCommand request, CancellationToken cancellationToken)
        {
            string sql = "SELECT TOP 1 * FROM [dbo].[SheepRescission] WHERE EarTag=@EarTag ORDER BY SurrenderDate DESC ";
            var parameters = new List<SqlParameter>
            {
                    new SqlParameter("@EarTag", request.EarTag),
            };
            var result = await db.GetListDataBySql<SheepRescission>(sql, parameters.ToArray());

            // 判断是否存在数据
            if (result!=null)
            {
                foreach (var item in result)
                {
                    // 进行比较，如果当前耳号与最新的耳号相等，则调用删除方法
                    if (request.SurrenderDate == item.SurrenderDate && item.TransFerType == 1)
                    {
                        string sqls = @"DELETE FROM [dbo].[SheepRescission] 
                                        WHERE EarTag =@EarTag 
                                        AND SurrenderDate = (
                                          SELECT MAX(SurrenderDate) 
                                          FROM [dbo].[SheepRescission] 
                                          WHERE EarTag =@EarTag
                                        )";
                        var parameter = new List<SqlParameter>
                        {
                                new SqlParameter("@EarTag", request.EarTag),
                        };
                        var results = await db.ExecuteNonQuery(sqls, parameter.ToArray());
                        return results;
                    }
                    else
                    {
                        return -1;
                    }

                }
            }
            return 0;
        }
        /// <summary>
        /// 转舍修改
        /// </summary>
        /// <param name="request"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<int> Handle(TransFerUpdateCommand request, CancellationToken cancellationToken)
        {
            string sql = "UPDATE  SheepRescission SET  ShiftToBuild=@ShiftToBuild,ShiftToHuts=@ShiftToHuts,ShiftToBuildingManagementId=@ShiftToBuildingManagementId,ShiftToColumnManagementId=@ShiftToColumnManagementId,UpdateBy=@UpdateBy,UpdateDate=@UpdateDate WHERE SheepRescissionId=@SheepRescissionId";
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@ShiftToBuild",request.ShiftToBuild),
                new SqlParameter("@ShiftToHuts",request.ShiftToHuts),
                new SqlParameter("@ShiftToBuildingManagementId",request.ShiftToBuildingManagementId),
                new SqlParameter("@ShiftToColumnManagementId",request.ShiftToColumnManagementId),
                new SqlParameter("@UpdateBy",request.UpdateBy),
                new SqlParameter("@UpdateDate",DateTime.Now),
                new SqlParameter("@SheepRescissionId",request.SheepRescissionId),
            };
            int result =await db.ExecuteNonQuery(sql, parameters);
            return result;
        }
        /// <summary>
        /// 批量删除
        /// </summary>
        /// <param name="request"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public async Task<string> Handle(TransFerDeleteMore request, CancellationToken cancellationToken)
        {
                // 执行查询操作
            string sql = "SELECT * FROM SheepRescission WHERE SheepRescissionId IN (SELECT value FROM STRING_SPLIT(@Ids, ','))";
            var parameter = new List<SqlParameter>
            {
                new SqlParameter("@ids", request.Ids),
            };
            var result = await db.GetListDataBySql<SheepRescission>(sql, parameter.ToArray());


            string sqls = "SELECT TOP(1) * FROM SheepRescission WHERE SheepRescissionId IN (SELECT value FROM STRING_SPLIT(@Ids, ',')) ORDER BY SurrenderDate DESC";
            var parameters = new List<SqlParameter>
            {
                new SqlParameter("@ids", request.Ids),
            };
            var results = await db.GetListDataBySql<SheepRescission>(sqls, parameters.ToArray());


            string sqlssssss = "SELECT TOP 1 * FROM [dbo].[SheepRescission] WHERE EarTag=@EarTag ORDER BY SurrenderDate DESC";
            var parametersss = new List<SqlParameter>
            {
                new SqlParameter("@EarTag",results.FirstOrDefault().EarTag),
            };
            var resultsss = await db.GetListDataBySql<SheepRescission>(sqlssssss, parametersss.ToArray());
            // 判断结果集是否为空
            foreach (var item in result)
            {
                if(item.TransFerType==0)
                {
                    string write = $"{item.SheepRescissionId}为事件转舍,不能删除";
                    return write;
                }
                else if(resultsss.FirstOrDefault().SurrenderDate == item.SurrenderDate && item.TransFerType == 1)
                {
                    string sqlss = @"DELETE FROM [dbo].[SheepRescission] 
                                        WHERE EarTag =@EarTag 
                                        AND SurrenderDate = (
                                          SELECT MAX(SurrenderDate) 
                                          FROM [dbo].[SheepRescission] 
                                          WHERE EarTag =@EarTag
                                        )";
                        var parameterss = new List<SqlParameter>
                        {
                                new SqlParameter("@EarTag", item.EarTag),
                        };
                    var resultss = await db.ExecuteNonQuery(sqlss, parameterss.ToArray());
                    return "删除成功";
                }
            }
            return "0";
        }
    }
}
